﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Configuration;
using System.Data.Common;
using System.Data;
using System.Collections;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Data.Odbc;
using System.Reflection;

namespace Jxym.DBUtility
{
    public class Utility
    {
        /// <summary>
        /// 哈希表来存储缓存的参数
        /// </summary>
        private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());
        public static readonly string connectionStringName = ConfigurationManager.AppSettings["ConnectionStringName"];
        public static readonly string ConnectionString = ConfigurationManager.ConnectionStrings[connectionStringName].ConnectionString;
        public static readonly string providerName = ConfigurationManager.ConnectionStrings[connectionStringName].ProviderName;
        public static DbProviderFactory _factory = null;
        private static DbCommand cmd = null;
        private static DbConnection conn = null;

        static Utility()
        {
            if (_factory == null)
            {
                CreateFactory();
            }
        }

        /// <summary>
        /// 创建数据库驱动实例
        /// </summary>
        /// <param name="providerName">数据库驱动名称</param>
        private static void CreateFactory()
        {
            switch (providerName)
            {
                case "System.Data.SqlClient":
                    _factory = SqlClientFactory.Instance;
                    break;
                case "System.Data.OleDb":
                    _factory = OleDbFactory.Instance;
                    break;
                case "System.Data.Odbc":
                    _factory = OdbcFactory.Instance;
                    break;
                default:
                    _factory = OdbcFactory.Instance;
                    break;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        public static void Close()
        {
            if (cmd != null) cmd.Dispose();
            if (conn.State != ConnectionState.Closed) conn.Close();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="conn"></param>
        /// <param name="trans"></param>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="cmdParms"></param>
        private static void PrepareCommand(IDbCommand cmd, IDbConnection conn, IDbTransaction trans, CommandType cmdType, string cmdText, DbParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }

            cmd.Connection = conn;

            if (trans != null)
            {
                cmd.Transaction = trans;
            }

            cmd.CommandText = cmdText;

            cmd.CommandType = cmdType;

            if (cmdParms != null)
            {
                cmd.Parameters.Clear();

                foreach (DbParameter parame in cmdParms)
                {
                    cmd.Parameters.Add(parame);
                }
            }
        }

        /// <summary>
        /// 添加参数数组的缓存
        /// </summary>
        /// <param name="cacheKey">缓存键名</param>
        /// <param name="cmdParms">要被缓存的数组。例如：SqlParamters</param>
        public static void CacheParameters(string cacheKey, params DbParameter[] commandParameters)
        {
            parmCache[cacheKey] = commandParameters;
        }

        /// <summary>
        /// 检索缓存的参数
        /// </summary>
        /// <param name="cacheKey">键</param>
        /// <returns>缓存参数数组</returns>
        public static DbParameter[] GetCachedParameters(string cacheKey)
        {
            DbParameter[] cachedParms = (DbParameter[])parmCache[cacheKey];

            if (cachedParms == null)
            {
                return null;
            }

            return cachedParms;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, DbParameter[] commandParameters)
        {
            using (cmd = _factory.CreateCommand())
            {
                using (conn = _factory.CreateConnection())
                {
                    try
                    {
                        conn.ConnectionString = connectionString;
                        PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                        return ExecuteNonQuery(conn, cmdType, cmdText, commandParameters);
                    }
                    catch { Close(); return 0; }
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static int ExecuteNonQuery(IDbConnection connection, CommandType cmdType, string cmdText, DbParameter[] commandParameters)
        {
            using (cmd = _factory.CreateCommand())
            {
                try
                {
                    PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
                    int val = cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                    return val;
                }
                catch { Close(); return 0; }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="trans"></param>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static int ExecuteNonQuery(DbTransaction trans, CommandType cmdType, string cmdText, DbParameter[] commandParameters)
        {
            using (cmd = _factory.CreateCommand())
            {
                try
                {
                    PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
                    int val = cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                    return val;
                }
                catch { Close(); return 0; }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static IDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, DbParameter[] commandParameters)
        {
            //如果该方法抛出异常，关闭连接，因为没有DataReader存在，因此commandBehaviour.CloseConnection将无法正常工作
            using (cmd = _factory.CreateCommand())
            {
                try
                {
                    conn = _factory.CreateConnection();

                    conn.ConnectionString = connectionString;

                    PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);

                    IDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                    cmd.Parameters.Clear();

                    return rdr;
                }
                catch { Close(); return null; }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static IDataReader ExecuteReader(DbConnection conn, CommandType cmdType, string cmdText, DbParameter[] commandParameters)
        {
            //如果该方法抛出异常，关闭连接，因为没有DataReader存在，因此commandBehaviour.CloseConnection将无法正常工作
            using (cmd = _factory.CreateCommand())
            {
                try
                {
                    PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);

                    IDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                    cmd.Parameters.Clear();

                    return rdr;
                }
                catch { Close(); return null; }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, DbParameter[] commandParameters)
        {
            using (cmd = _factory.CreateCommand())
            {
                using (conn = _factory.CreateConnection())
                {
                    try
                    {
                        conn.ConnectionString = connectionString;
                        PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                        object val = cmd.ExecuteScalar();
                        cmd.Parameters.Clear();
                        return val;
                    }
                    catch { Close(); return null; }
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static object ExecuteScalar(DbConnection connection, CommandType cmdType, string cmdText, DbParameter[] commandParameters)
        {
            using (cmd = _factory.CreateCommand())
            {
                try
                {
                    PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
                    object val = cmd.ExecuteScalar();
                    cmd.Parameters.Clear();
                    return val;
                }
                catch { Close(); return null; }
            }
        }

        /// <summary>
        /// 模型赋值
        /// </summary>
        /// <param name="info">模型类实例</param>
        /// <param name="sdr">IDataReader实例</param>
        /// <returns>带值模型实例</returns>
        public static object ModelValue(object info, IDataReader sdr)
        {
            PropertyInfo[] properties = info.GetType().GetProperties();

            foreach (PropertyInfo pi in properties)
            {
                pi.SetValue(info, SDR(sdr[pi.Name]), null);
            }

            return info;
        }

        /// <summary>
        /// 实例默认值效验
        /// </summary>
        /// <param name="value"></param>
        /// <returns></returns>
        public static object SDR(object value)
        {
            switch (value.GetType().Name)
            {
                case "Int16": return value != DBNull.Value ? Convert.ToInt16(value) : 0;
                case "Int32": return value != DBNull.Value ? Convert.ToInt32(value) : 0;
                case "Int64": return value != DBNull.Value ? Convert.ToInt64(value) : 0;
                case "Boolean": return value != DBNull.Value ? Convert.ToBoolean(value) : false;
                case "Byte": return value != DBNull.Value ? Convert.ToByte(value) : 0;
                case "Char": return value != DBNull.Value ? Convert.ToChar(value) : '-';
                case "Decimal": return value != DBNull.Value ? Convert.ToDecimal(value) : 0;
                case "SByte": return value != DBNull.Value ? Convert.ToSByte(value) : 0;
                case "Single": return value != DBNull.Value ? Convert.ToSingle(value) : 0;
                case "UInt16": return value != DBNull.Value ? Convert.ToUInt16(value) : 0;
                case "UInt32": return value != DBNull.Value ? Convert.ToUInt32(value) : 0;
                case "UInt64": return value != DBNull.Value ? Convert.ToUInt64(value) : 0;
                case "DateTime": return value != DBNull.Value ? Convert.ToDateTime(value) : DateTime.MinValue;
                case "Double": return value != DBNull.Value ? Convert.ToDouble(value) : 0;
                default: return value != DBNull.Value ? value.ToString() : "-";
            }
        }
    }
}
